/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.PenelitiEksternal;
import Entity.Penelitian;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import library.Koneksi;

/**
 *
 * @author user
 * model untuk menangani operasi database berkaitan dengan peneliti eksternal
 */
public class DaftarPenelitiEksternal {

    PenelitiEksternal penelitiEksternal;

    //method digunakan untuk menambah data peneliti eksternal
    public boolean tambahPenelitiEksternal(PenelitiEksternal penelitiEksternal) {

        boolean hasil = false;
        this.penelitiEksternal = penelitiEksternal;

        Connection conn = Koneksi.getKoneksi();
        PreparedStatement st = null;
        if (this.penelitiEksternal.getPenelitian().getUniqueKey() != "") {
            ResultSet rs1 = null;
            try {
                String sql1 = "SELECT NO_PENELITIAN FROM PENELITIAN_DOSEN WHERE UNIQUEKEY = '" + this.penelitiEksternal.getPenelitian().getUniqueKey() + "'";
                System.out.println(sql1);

                st = conn.prepareStatement(sql1);

                rs1 = st.executeQuery();

                while (rs1.next()) {
                    Penelitian penelitian = new Penelitian();
                    penelitian.setNoPenelitian(rs1.getInt("NO_PENELITIAN"));
                    this.penelitiEksternal.setPenelitian(penelitian);
                }

            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
                }
                try {
                    rs1.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        String sql = "insert into peneliti_eksternal(NO_PENELITIAN, NAMA_PENELITI) "
                + "value(" + this.penelitiEksternal.getPenelitian().getNoPenelitian()
                + ", '" + this.penelitiEksternal.getNamaPeneliti()
                + "')";

        System.out.println(sql);

        try {

            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate();
            if (rs > 0) {
                hasil = true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return hasil;

    }

    //method digunakan untuk menghapus data peneliti eksternal
    public boolean hapusPenelitiEksternal(PenelitiEksternal penelitiEksternal) {

        boolean hasil = true;

        PreparedStatement st = null;

        String sql = "DELETE FROM peneliti_eksternal WHERE ID_PENELITI_EKSTERNAL = " + penelitiEksternal.getIdPenelitiEksternal();
        System.out.println(sql);
        Connection conn = Koneksi.getKoneksi();
        try {
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }

    //method digunakan untuk mengambil daftar peneliti eksternal
    public List<PenelitiEksternal> ambilDaftarPenelitiEks(Penelitian penelitian) {
        List<PenelitiEksternal> daftarPenelitiEksternal = new ArrayList<PenelitiEksternal>();

        String sql = "SELECT * FROM peneliti_eksternal, penelitian_dosen "
                + "WHERE penelitian_dosen.NO_PENELITIAN = peneliti_eksternal.NO_PENELITIAN "
                + "AND penelitian_dosen.NO_PENELITIAN=" + penelitian.getNoPenelitian();
        PreparedStatement st = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            ResultSet rs = st.executeQuery();

            while (rs.next()) {
                this.penelitiEksternal = new PenelitiEksternal();
                
                this.penelitiEksternal.setIdPenelitiEksternal(rs.getInt("ID_PENELITI_EKSTERNAL"));
                this.penelitiEksternal.setPenelitian(penelitian);
                this.penelitiEksternal.setNamaPeneliti(rs.getString("NAMA_PENELITI"));

                daftarPenelitiEksternal.add(this.penelitiEksternal);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitiEksternal.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarPenelitiEksternal;
    }
}
